<?php
/**
 * Created by PhpStorm.
 * User: yy
 * Date: 2019/3/21
 * Time: 17:30
 */

namespace app\common\Service\airquality;

use think\facade\Config;
use app\common\service\BaseService;
use app\common\service\airquality\AirQualityStationAqiRealtimeDayService;
use app\common\service\airquality\AqiService;
use think\Db;

class AirQualityRankingStatisticsService extends BaseService
{
    /**
     * AirQualityStationAqiRealtimeDayService constructor.
     */
    protected $aqiService;

    public function __construct()
    {
        parent::__construct();
        $this->aqiService = new AqiService();
    }

    /**
     *  排名统计左侧表格
     * @author yy by 2019/5/9
     * @param array $param
     * @return array
     */
    public function getTable($param = [])
    {
        #判断日期类型 注意年月日不同日期类型的查询返回参数不同
        if ($param['date_type'] == 'year') {
            $norm = '%Y';
        } elseif ($param['date_type'] == 'month') {
            $norm = '%Y-%m';
        } else {
            $norm = '%Y-%m-%d';
        }
        $result         = [];
        $table_data     = [];
        $pm25_echart    = [];
        $aqi_echart     = [];
        $primary_echart = [];

        # PM2.5值（查询两次的原因是不能确定返回的结果集中是否包含所有站点）从高到低排序
        $changzhi_id  = Config::get('changzhi_id');
        $pm25_average = Db::query("select name,station_id from basic_station where station_id <> $changzhi_id and region_id = ?", [$param['region_id']]);
        $pm25_arr     = Db::query("select round(avg(a.pm25),2) as pm25_average,a.station_id,b.name
                        from air_quality_station_aqi_realtime_day as a
                        left join basic_station as b on a.station_id = b.station_id
                        where a.station_id <> $changzhi_id and a.region_id =? and DATE_FORMAT(a.publish_time, '$norm') =?
                        group by a.station_id order by pm25_average desc", [$param['region_id'], $param['look_time']]);
        foreach ($pm25_average as $k1 => &$v1) {
            $v1['pm25_average'] = '-';
            foreach ($pm25_arr as $k => $v) {
                ($v['station_id'] == $v1['station_id']) ? ($v1['pm25_average'] = round($v['pm25_average'])) : '-';
            }
        }
        # 按照PM2.5进行排序
        $pm25_value = array_column($pm25_average, 'pm25_average');
        array_multisort($pm25_value, SORT_DESC, $pm25_average);
        # PM2.5echart图表数据
        foreach ($pm25_average as $k25 => $v25) {
            $pm25_echart['date'][]    = $v25['name'];
            $pm25_echart['data'][0][] = $v25['pm25_average'];
        }
        # pm2.5同比下降百分比(和一年前先比)
        if ($param['date_type'] == 'year') {
            $look_time    = $param['look_time'] . '-01-01';
            $history_time = (int)$param['look_time'] - 1;
//            $history_rate = Db::query("select round(avg(pm25),2) as pm25_average_history,station_id
//                        from air_quality_station_aqi_realtime_day
//                        where region_id =:region_id and DATE_FORMAT(publish_time, '$norm') = DATE_SUB(:look_time,INTERVAL 1 YEAR)
//                        group by station_id", ['region_id' => $param['region_id'], 'look_time' => $param['look_time']]);
        } elseif ($param['date_type'] == 'month') {
            $look_time    = $param['look_time'] . '-01';
            $history_year = (int)date('Y', strtotime($look_time)) - 1;
            $look_month   = date('m', strtotime($look_time));
            $history_time = $history_year . '-' . $look_month;
        } else {
            $look_time      = $param['look_time'];
            $history_year   = (int)date('Y', strtotime($look_time)) - 1;
            $look_month_day = date('m-d', strtotime($look_time));
            $history_time   = $history_year . '-' . $look_month_day;
        }
        $history_rate = Db::query("select round(avg(pm25),2) as pm25_average_history,station_id
                        from air_quality_station_aqi_realtime_day
                        where station_id <> $changzhi_id and region_id =? and DATE_FORMAT(publish_time, '$norm') = ?
                        group by station_id", [$param['region_id'], $history_time]);
        foreach ($pm25_average as $k4 => &$v4) {
            $v4['down_rate'] = '-';
            foreach ($history_rate as $k5 => $v5) {
                if ($v5['station_id'] == $v4['station_id']) {
                    $v4['down_rate'] = (round(($v5['pm25_average_history'] - $v4['pm25_average']) / $v5['pm25_average_history'], 2) * 100) . '%';
                }
            }
        }
        if ($param['date_type'] == 'day') {
            # 获取六参平均值（臭氧参数选取一天中最大小时平均数）(这里只能查看历史数据--实测表，所以查publish_time这个字段)
            $six_data = Db::query("select aqi, round(avg(pm10),2) as pm10_average,round(avg(pm25),2) as pm25_average,round(avg(so2),2) as so2_average,round(avg(no2),2) as no2_average,round(avg(co),2) as co_average,round(max(o3),2) as o3_average_max,station_id
                        from air_quality_station_aqi_realtime_day
                        where  station_id <> $changzhi_id and region_id =? and DATE_FORMAT(publish_time, '$norm') = ?
                        group by station_id", [$param['region_id'], $param['look_time']]);
            # 获取天查询AQI
            foreach ($six_data as $k7 => &$v7) {
//                $arr = $this->aqiService->getAqi($v7, false, false, false);
//                $arr = $this->aqiService->getAqi([ 'pm25_average' =>48.0833, 'pm10_average' =>104.125, 'co_average' => 1.27917, 'no2_average' => 16.875, 'o3_average_max' => 170, 'so2_average' => 10.5], 'true','true',false);
                # 从数据库查询aqi 考虑 ‘’和-9999的情况
                $v7['aqi']          = ($v7['aqi'] && $v7['aqi'] >= 0) ? ceil($v7['aqi']) : '-';
                $v7['pm25_average'] = ($v7['pm25_average'] && $v7['pm25_average'] >= 0) ? round($v7['pm25_average']) : '-';
            }
            #日返回参数格式处理
            foreach ($pm25_average as $k9 => &$v9) {
                $v9['aqi']          = '-';
                $v9['pm25_average'] = '-';
                foreach ($six_data as $k10 => $v10) {
                    ($v10['station_id'] == $v9['station_id']) ? ($v9['aqi'] = $v10['aqi']) : '';
                    ($v10['station_id'] == $v9['station_id']) ? ($v9['pm25_average'] = $v10['pm25_average']) : '';
                }
            }
            # 从高到低排序
            $aqi = array_column($pm25_average, 'aqi');
            array_multisort($aqi, SORT_DESC, $pm25_average);
            foreach ($pm25_average as $k => $v) {
                $aqi_echart['date'][]    = $v['name'];
                $aqi_echart['data'][0][] = $v['aqi'];
            }
            $result = ['table_data' => $pm25_average, 'pm25_echart' => $pm25_echart, 'aqi_echart' => $aqi_echart];
        } else {
            # 优良天数和重污染天数获取
            $level_data = Db::query("select count(case when aqi between 0 and 100 then aqi end) as day_good,count(case when aqi between 200 and 500 then aqi end) as day_bad,station_id
                            from air_quality_station_aqi_realtime_day
                            where  station_id <> $changzhi_id and region_id =:region_id and DATE_FORMAT(publish_time, '$norm') =:look_time
                            group by station_id", ['region_id' => $param['region_id'], 'look_time' => $param['look_time']]);
            # 不同站点的首要污染物（可能多个）及天数(不计算占比)
            $station_all                = Db::query("select name,station_id from basic_station where station_id <> $changzhi_id and region_id = ?", [$param['region_id']]);
            $primary_pollutant_data_old = Db::query("select count(a.id) as day_primary_pollutant,a.name as primary_pollutant_name,a.station_id,b.name
                            from air_quality_station_primary_pollutant_realtime_day as a
                            left join basic_station as b on b.station_id = a.station_id
                            where  b.station_id <> $changzhi_id and a.region_id =:region_id and DATE_FORMAT(a.publish_time, '$norm') =:look_time
                            group by a.station_id,a.name", ['region_id' => $param['region_id'], 'look_time' => $param['look_time']]);
            $primary_pollutant_data     = Db::query("select a.name as primary_pollutant_name,a.station_id,b.name,DATE_FORMAT(a.publish_time, '%Y-%m-%d') as date
                            from air_quality_station_primary_pollutant_realtime_day as a
                            left join basic_station as b on b.station_id = a.station_id
                            where  b.station_id <> $changzhi_id and a.region_id = ? and DATE_FORMAT(a.publish_time, '$norm') = ?
                            ", [$param['region_id'], $param['look_time']]);
            $new                        = [];
            if (count($primary_pollutant_data) > 0) {
                foreach ($primary_pollutant_data as $kp => $vp) {
                    $new[$vp['station_id']][strtoupper($vp['primary_pollutant_name'])][] = $vp['date'];
                }
                # 将O3 和 O3_8H 存在于同一天和 单天只存在O3_8H的情况统一处理为O3
                foreach ($new as $kn => &$vn) {
                    # 查询日期区间 O3 和 O3_8H 同时存在，但是日期不同(将差别日期补到O3中)
                    if (isset($vn['O3']) && isset($vn['O3_8H'])) {
                        # 比较差集
                        $different     = array_values(array_diff($vn['O3_8H'], $vn['O3']));
                        $different_num = count($different);
                        if ($different_num > 0) {
                            for ($i = 0; $i < $different_num; $i++) {
                                $vn['O3'][] = $different[$i];
                            }
                        }
                    }
                    # 查询日期区间只存在O3_8H （将日期全部补到O3中）
                    if (!isset($vn['O3']) && isset($vn['O3_8H'])) {
                        $o3_8h_num = count($vn['O3_8H']);
                        for ($m = 0; $m < $o3_8h_num; $m++) {
                            $vn['O3'][] = $vn['O3_8H'][$m];
                        }
                    }
                }
                # 统计各个站点下各首要污染物出现的天数
                foreach ($new as $ks => &$vs) {
                    foreach ($vs as $km => &$vm) {
                        $vm = count($vm);
                    }
                }
            }
            # 处理部分站点存在多个首要污染物，但是部分站点一个首要污染物也没有的情况
            $station_all_name      = array_unique(array_column($primary_pollutant_data, 'name'));
            $primary_pollutant_arr = [];
            # 将数据处理成上个版本的格式方便返给前台要求格式
            foreach ($station_all as $k6 => $v6) {
                if (!in_array($v6['name'], $station_all_name)) {
                    $primary_pollutant_arr[] = [
                        'station_id'             => $v6['station_id'],
                        'name'                   => $v6['name'],
                        'day_primary_pollutant'  => '-',
                        'primary_pollutant_name' => '-',
                    ];
                } else {
                    foreach ($new as $ka => $va) {
                        if ($ka == $v6['station_id']) {
                            foreach ($va as $kb => $vb) {
                                $primary_pollutant_arr[] = [
                                    'station_id'             => $v6['station_id'],
                                    'name'                   => $v6['name'],
                                    'day_primary_pollutant'  => $vb,
                                    'primary_pollutant_name' => $kb,
                                ];
                            }
                        }
                    }
                }
            }
            #年月返回格式处理
            foreach ($primary_pollutant_arr as $k2 => &$v2) {
                $v2['primary_pollutant_name'] = strtoupper($v2['primary_pollutant_name']);
                $v2['primary_pollutant_name'] === 'PM25' ? $v2['primary_pollutant_name'] = 'PM2.5' : '';
                foreach ($level_data as $k3 => $v3) {
                    ($v3['station_id'] == $v2['station_id']) ? $table_data[$v2['station_id']]['day_good'] = $v3['day_good'] : '';
                    ($v3['station_id'] == $v2['station_id']) ? $table_data[$v2['station_id']]['day_bad'] = $v3['day_bad'] : '';
                }
                $table_data[$v2['station_id']]['station_id'] = $v2['station_id'];
                $table_data[$v2['station_id']]['name']       = $v2['name'];
                if ($v2['primary_pollutant_name'] != '-' && strtoupper($v2['primary_pollutant_name']) != 'O3_8H') {
                    $table_data[$v2['station_id']][str_replace('.', '', strtolower($v2['primary_pollutant_name'])) . '_day'] = $v2['day_primary_pollutant'];
                    $table_data[$v2['station_id']]['primary_pollutant'][]                                                    = $v2['primary_pollutant_name'] . '(' . $v2['day_primary_pollutant'] . ')';
//            $table_data[$v2['station_id']][$v2['primary_pollutant_name'] . '_rate'] = $v2['rate_primary_pollutant'];
                }
                foreach ($pm25_average as $k => $v) {
                    ($v['station_id'] == $v2['station_id']) ? $table_data[$v2['station_id']]['pm25_average'] = $v['pm25_average'] : '';
                    ($v['station_id'] == $v2['station_id']) ? $table_data[$v2['station_id']]['down_rate'] = $v['down_rate'] : '';
                }
            }
            # 首要污染物echart图表数据
            $primary_data = [];
            foreach ($table_data as $k => $v) {
                $primary_data[$k]['name']  = $v['name'];
                $primary_data[$k]['PM2.5'] = isset($v['pm25_day']) ? $v['pm25_day'] : '';
                $primary_data[$k]['PM10']  = isset($v['pm10_day']) ? $v['pm10_day'] : '';
                $primary_data[$k]['SO2']   = isset($v['so2_day']) ? $v['so2_day'] : '';
                $primary_data[$k]['NO2']   = isset($v['no2_day']) ? $v['no2_day'] : '';
                $primary_data[$k]['CO']    = isset($v['co_day']) ? $v['co_day'] : '';
                $primary_data[$k]['O3']    = isset($v['o3_day']) ? $v['o3_day'] : '';
            }
            $primary_data = array_values($primary_data);
            foreach ($primary_data as $k => $v) {
                $primary_echart['date'][]    = $v['name'];
                $primary_echart['data'][0][] = ['name' => 'PM10', 'value' => $v['PM10']];
                $primary_echart['data'][1][] = ['name' => 'PM2.5', 'value' => $v['PM2.5']];
                $primary_echart['data'][2][] = ['name' => 'SO2', 'value' => $v['SO2']];
                $primary_echart['data'][3][] = ['name' => 'NO2', 'value' => $v['NO2']];
                $primary_echart['data'][4][] = ['name' => 'O3', 'value' => $v['O3']];
                $primary_echart['data'][5][] = ['name' => 'CO', 'value' => $v['CO']];
            }
            $result = ['table_data' => array_values($table_data), 'pm25_echart' => $pm25_echart, 'primary_echart' => $primary_echart];
//        # 日期范围相差天数
//        $airQualityStationAqiRealtimeDayService = new AirQualityStationAqiRealtimeDayService();
//        $days                                   = $airQualityStationAqiRealtimeDayService->diffDays($param['start_time'], $param['end_time']);
//        foreach ($primary_pollutant_data as $k1 => &$v1) {
//            $v1['rate_primary_pollutant'] = (round($v1['day_primary_pollutant'] / $days, 2) * 100) . '%';
//        }
        }
        # PM2.5 增加单位 . ' μg/m³'
        foreach ($result['table_data'] as $kpm => &$vpm) {
            $vpm['pm25_average'] = $vpm['pm25_average'] > 0 ? $vpm['pm25_average'] . ' μg/m³' : $vpm['pm25_average'];
        }
        return $result;
    }
}